with     three_segment_different as (
         select express_org_code, express_center_short_code
         from jms_dim.dim_yl_sys_package_different_base
         group by express_org_code, express_center_short_code
     ),

     three_segment_site as
         (
             select
                 t1.name,
                 t1.code,
                 t1.agent_code,
                 t1.agent_name,
                 t1.provider_id,
                 t1.provider_desc,
                 t1.city_id,
                 t1.city_desc,
                 t1.area_id,
                 t1.area_desc,
                 max(t2.express_center_short_code) as first_code_package,
                 max(t3.code) as sj_second_code,
                 concat_ws(',',collect_set(t4.code)) as first_code

             from
                 jms_dim.dim_network_whole_massage t1
                     left join three_segment_different t2 on t1.code=t2.express_org_code
                     left join jms_dim.dim_lmdm_sys_second_code t3 on t1.code=t3.network_code and  t3.is_enable='1' and t3.is_delete='1'
                     left join jms_dim.dim_lmdm_sys_first_code t4 on t1.provider_id=t4.destination_province_id
                     and t1.city_id=t4.destination_city_id and t1.area_id =t4.destination_area_id
                     and  t4.is_enable='1' and t4.is_delete='1'

             group by
                 t1.name,
                 t1.code,
                 t1.agent_code,
                 t1.agent_name,
                 t1.provider_id,
                 t1.provider_desc,
                 t1.city_id,
                 t1.city_desc,
                 t1.area_id,
                 t1.area_desc
         )
       
insert overwrite table jms_dm.dm_lmdm_three_segment_map_dt partition (dt)    
select 
    t.is_refund
    ,t.pick_network_code
    ,t.pick_network_name
    ,t.sign_finance_code
    ,t.sign_finance_name
    ,t.sign_network_code
    ,t.sign_network_name
    ,t.sign_time
    ,t.waybill_no
    ,t.sj_first_code
    ,t.sj_second_code
    ,t.sj_third_code
    ,t.dispatch_staff_code
    ,t.dispatch_staff_name
    ,t.receiver_province_id
    ,t.receiver_province_name
    ,t.receiver_city_id
    ,t.receiver_city_name
    ,t.receiver_area_id
    ,t.receiver_area_name
    ,t.receiver_township
    ,t.receiver_street
    ,t.receiver_detailed_address
    ,t.date_time
    ,t.dt
 from (             
    select 
     case when t.is_refund =1 then 1 else 0 end is_refund  --是否退件,1是，0否    
    ,t.pick_network_code --寄件网点code    
    ,t.pick_network_name --寄件网点名称    
    ,t.sign_finance_code --签收财务网点编码    
    ,t.sign_finance_name --签收财务网点名称    
    ,t.sign_network_code --签收网点code    
    ,t.sign_network_name --签收网点名称    
    ,t.sign_time   --签收时间    
    ,t.waybill_no  --运单号    
    ,case when t2.first_code_package is not  null then t2.first_code_package else t2.first_code end as sj_first_code --签收三段码    
    ,t2.sj_second_code --签收三段码    
    ,t4.tail_code  as sj_third_code --签收三段码    
    ,t.dispatch_staff_code --派件业务员code    
    ,t.dispatch_staff_name --派件业务员名称    
    ,to_date(t.sign_time) as date_time  --dt
    ,to_date(t.sign_time) as dt     --dt
    ,row_number()over(partition by t.waybill_no order by t.sign_time desc ) as rn
    ,t.receiver_province_id
    ,t.receiver_province_name
    ,t.receiver_city_id
    ,t.receiver_city_name
    ,t.receiver_area_id
    ,t.receiver_area_name
    ,t.receiver_township
    ,t.receiver_street
    ,t.receiver_detailed_address
    from  jms_dwd.dwd_yl_oms_oms_waybill_incre_dt t
    left join  three_segment_site t2 on t.dispatch_network_code=t2.code
    left join  (
                 select  t1.staff_code,
                         t1.tail_code
                 from jms_dim.dim_lmdm_sys_tail_code t
                          join jms_dim.dim_lmdm_sys_tail_code_staff t1 on t.id=t1.tail_id
                 where   t.is_enable='1' and t.is_delete='1'
                 group by  t1.staff_code, t1.tail_code
             ) t4 on t.dispatch_staff_code=t4.staff_code
    where t.dt between date_add('{{ execution_date  | cst_ds }}', -30) and '{{ execution_date  | cst_ds }}'
    and to_date(sign_time)='{{ execution_date  | cst_ds }}'
    and t.is_sign='1'
) t where t.rn=1
distribute by pmod(hash(rand()), 200)
;
